package com.qymerge.manage.system.dao.impl;

import com.qymerge.manage.core.bean.PageRequest;
import com.qymerge.manage.core.dao.BaseDao;
import com.qymerge.manage.system.ro.SysRoleRO;
import com.qymerge.manage.system.dao.ISysRoleDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *<p>Title: SysRoleDao</p>
 *<p>Description: </p>
 *
 *@Author cong.zhang
 *@Version 1.0.0
 *@Date 2020/7/26/0026
 *@Time 23:02
 */
@Repository
public class SysRoleDaoImpl extends BaseDao implements ISysRoleDao {

    /**
     *
     * @param ro
     * @return
     */
    @Override
    public List<SysRoleRO> list(SysRoleRO ro, PageRequest pageRequest) {
        int skip = (pageRequest.getPage() - 1) * pageRequest.getSize();
        int size = pageRequest.getSize();

        String sql = "select * from dwd_MCRS_MDM_sys_role where 1=1";
        List<Object> queryList=new ArrayList<Object>();

        if (StringUtils.isNotEmpty(ro.getRole_name())) {
            sql += " and role_name like ?";
            queryList.add("%" + ro.getRole_name() + "%");
        }

        // 解决页面不传状态问题
        if (null != ro.getStatus()) {
            if(-1 != ro.getStatus()) {
                sql += " and stc.status=?";
                queryList.add(ro.getStatus());
            }
        }

        // 分页数据获取
        sql += " order by create_time desc limit ?,?";
        queryList.add(skip);
        queryList.add(size);

        RowMapper<SysRoleRO> rowMapper = new BeanPropertyRowMapper<SysRoleRO>(SysRoleRO.class);
        List<SysRoleRO> list = getJdbcTemplate().query(sql, rowMapper, queryList.toArray());
        return list;
    }

    /**
     *
     * @param ro
     * @return
     */
    @Override
    public int total(SysRoleRO ro) {
        String sql = "select count(1) from dwd_MCRS_MDM_sys_role where 1=1";
        List <Object> queryList = new ArrayList<Object>();

        // 解决页面不传状态问题
        if (null != ro.getStatus()) {
            if (-1 != ro.getStatus()) {
                sql += " and stc.status=?";
                queryList.add(ro.getStatus());
            }
        }

        return getJdbcTemplate().queryForObject(sql, Integer.class, queryList.toArray());
    }

    /**
     *
     * @param id
     * @return
     */
    @Override
    public SysRoleRO findOneById(Long id) {
        String sql = "select * from dwd_MCRS_MDM_sys_role where id = ?";
        RowMapper<SysRoleRO> rowMapper = new BeanPropertyRowMapper<SysRoleRO>(SysRoleRO.class);

        try {
            return getJdbcTemplate().queryForObject(sql, rowMapper, id);
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public SysRoleRO findOneByRoleName(String roleName) {
        String sql = "select * from dwd_MCRS_MDM_sys_role where role_name = ?";
        RowMapper<SysRoleRO> rowMapper = new BeanPropertyRowMapper<SysRoleRO>(SysRoleRO.class);

        try {
            return getJdbcTemplate().queryForObject(sql, rowMapper, roleName);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据用户角色中间表查询角色信息
     *
     * @param userId
     * @return
     */
    @Override
    public SysRoleRO findOneByUserId(Long userId) {
        String sql = "SELECT * FROM dwd_MCRS_MDM_sys_role WHERE id=(SELECT role_id FROM sys_user_role WHERE user_id=? AND STATUS=1 LIMIT 0,1) AND STATUS=1";

        try {
            RowMapper<SysRoleRO> rowMapper = new BeanPropertyRowMapper<SysRoleRO>(SysRoleRO.class);
            return getJdbcTemplate().queryForObject(sql, rowMapper, userId);
        } catch (DataAccessException e) {
            return null;
        }
    }


    /**
     * 角色增加
     *
     * @param ro
     * @return
     */
    @Override
    public int install(final SysRoleRO ro) {
        final String sql = "insert into dwd_MCRS_MDM_sys_role(role_name, role_desc, create_user_id, create_time, status) values(?,?,?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        this.getJdbcTemplate().update(
                new PreparedStatementCreator(){

                    @Override
                    public java.sql.PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                        int i = 0;
                        java.sql.PreparedStatement ps = conn.prepareStatement(sql);
                        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(++i, ro.getRole_name());
                        ps.setString(++i, ro.getRole_desc());

                        ps.setLong(++i, ro.getCreate_user_id());
                        ps.setLong(++i, ro.getCreate_time());
                        ps.setInt(++i, ro.getStatus());
                        return ps;
                    }
                }, keyHolder);

        return keyHolder.getKey().intValue();
    }

    /**
     *
     * @param ro
     * @return
     */
    @Override
    public int update(SysRoleRO ro) {
        String sql = "update dwd_MCRS_MDM_sys_role set role_name=?, role_desc=?, update_user_id=?, update_time=?, status=? where id=?";
        List param = new ArrayList();
        param.add(ro.getRole_name());
        param.add(ro.getRole_desc());

        param.add(ro.getCreate_user_id());
        param.add(ro.getCreate_time());
        param.add(ro.getStatus() );
        param.add(ro.getId() );

        return getJdbcTemplate().update(sql, param.toArray());
    }

    @Override
    public int remove(Long id) {
        // 删除
        String sql="delete from dwd_MCRS_MDM_sys_role where id=?";
        return getJdbcTemplate().update(sql, id);
    }


}
